package com.andnnl.oracle;

import java.sql.*;

/**
 * Created by chenss on 2023/5/20.
 */
public class OracleTest {
    public static void main(String[] args) {
        // Oracle JDBC 驱动程序类名
        String driver = "oracle.jdbc.driver.OracleDriver";
// 数据库连接 url
        // 注意：连接 CDB 时，端口号 1521 后是冒号 ":"，如 xxx:1521:ORCL
        // 连接 PDB 时，端口号 1521 后是斜杠 "/"，如 xxx:1521/ORCLPDB
// 连接信息
        String url = "jdbc:oracle:thin:@//192.168.4.251:1521/swjpdb";
        String user = "swj";
        String password = "123456";

// JDBC 连接池参数
        int maxActive = 10; // 最多并发连接数
        int maxIdle = 5; // 最多空闲连接数
        long maxWait = 10000; // 最大等待时间，毫秒

// 创建 JDBC 连接池对象

// 从池中获取连接
        Connection connection = null;

        try {
            Class.forName("oracle.jdbc.OracleDriver"); // 加载 Oracle JDBC 驱动程序
            connection = DriverManager.getConnection(url, user, password); // 获取数据库连接
            System.out.println("Success!");
            testGet(connection);
//            testInsert(connection);


        } catch (ClassNotFoundException e) {
            System.out.println("Oracle JDBC driver not found.");
        } catch (SQLException e) {
            System.out.println("Connection failed.");
            e.printStackTrace();
        } finally {
            if (connection != null) {
                try {
                    connection.close(); // 关闭数据库连接
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
    }

    private static void testGet(Connection connection) throws SQLException {
        Statement st = connection.createStatement();
//        String sql = "select \"parentId\" from \"t_area\"";
        String sql = "\n" +
                "SELECT * FROM ( \n" +
                "       SELECT beeltT.*, \n" +
                "              ROWNUM beetl_rn FROM (\n" +
                "                     select a.id,a.name from T_TEST1 a where 1 = 1 and id> 1 \n" +
                "                ) beeltT WHERE ROWNUM <20 \n" +
                "                ) \n" +
                "  WHERE beetl_rn >= 1";
//            String sql="select parentId from t_area";
        ResultSet rs = st.executeQuery(sql);
        while (rs.next()) {
//            String s = rs.getString(1);
            Long s = rs.getLong("id");
            String name = rs.getString("name");
            System.out.println(s+" "+name);
        }
    }

    private static void testInsert(Connection conn) throws SQLException {
//        String sql = "insert into \"t_mo\"\n" +
//                " (\"id\",\"userId\",\"depId\",\"taskId\",\"isTake\"\n" +
//                " ,\"mobile\",\"extNum\",\"recvNum\",\"recvTime\", \"msgId\"\n" +
//                " ,\"content\",\"pushStatus\",\"pushCode\",\"pushTime\",\"sendTime\"\n" +
//                " ,\"sendContent\",\"gwId\")\n" +
//                "\n" +
//                "  VALUES (mo_seq.nextval,448786472883982336, null, 448786472883982337, 0\n" +
//                "  , '18512341234', '001', 10690123, sysdate, 448786472883982338\n" +
//                "  , 'asdfklasdlkfalsdfjlk', 0, null, null, sysdate\n" +
//                "  , 'sdsdddddddddddd', 12311) ";

        String sql = "insert into T_TEST1" +
                " (ID,NAME)\n" +
                "  VALUES (mo_seq.nextval,'aaaaaaaaaaa') ";

//        PreparedStatement pstmt = conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
        PreparedStatement pstmt = conn.prepareStatement(sql, new String[] {"id"});
        // 执行 INSERT 语句
        int rs = pstmt.executeUpdate();
        System.out.println(rs);

        // 获取输出参数 :id 的值，即生成的序列值
//        ResultSet seqRs = pstmt.getResultSet();
        ResultSet seqRs = pstmt.getGeneratedKeys();
        seqRs.next();
        Object key = seqRs.getObject(1);
        System.out.println(key);

//            String sql="select parentId from t_area";
        // 定义输出参数 :id，并将其与程序中的变量绑定
//        PreparedStatement ps = conn.prepareStatement(sql, new String[]{"id"});
//        ps.setObject();
//            this.setPreparedStatementPara(ps, objs);

//    int ret = ps.executeUpdate();
//
//            if (idType == DBStyle.ID_AUTO || idType == DBStyle.ID_SEQ) {
//        ResultSet seqRs = ps.getGeneratedKeys();
//        seqRs.next();
//        Object key = seqRs.getObject(1);
//        holder.setKey(key);
//        seqRs.close();
//    }

    }
}
